import pool from '../database';
import type { FlightData } from '../../types/DataTypes';

export class FlightDAO {
    // 新增航班
    static async addFlight(flight: FlightData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `INSERT INTO Flight (flightNum, fromCity, arivCity, price, totalSeat, availSeat) 
                 VALUES (?, ?, ?, ?, ?, ?)`,
                [flight.flightNum, flight.fromCity, flight.arivCity, flight.price, flight.totalSeat, flight.availSeat]
            );
            return (result as any).affectedRows > 0;
        } catch (error: any) {
            if (error.code === 'ER_DUP_ENTRY') {
                throw new Error(`航班号 ${flight.flightNum} 已存在`);
            }
            throw error;
        } finally {
            connection.release();
        }
    }

    // 批量新增航班
    /*static async addFlights(flights: FlightData[]): Promise<number> {
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();

            let successCount = 0;
            for (const flight of flights) {
                try {
                    const [result] = await connection.execute(
                        `INSERT INTO Flight (flightNum, fromCity, arivCity, price, totalSeat, availSeat)
                         VALUES (?, ?, ?, ?, ?, ?)`,
                        [flight.flightNum, flight.fromCity, flight.arivCity, flight.price, flight.totalSeat, flight.availSeat]
                    );
                    if ((result as any).affectedRows > 0) {
                        successCount++;
                    }
                } catch (error: any) {
                    if (error.code !== 'ER_DUP_ENTRY') {
                        throw error;
                    }
                    // 重复记录跳过，继续插入其他记录
                }
            }

            await connection.commit();
            return successCount;
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }*/

    // 获取所有航班
    static async getAllFlights(): Promise<FlightData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Flight ORDER BY flightNum'
            );
            return rows as FlightData[];
        } finally {
            connection.release();
        }
    }

    // 根据航班号模糊查询航班
    static async searchFlightsByFlightNum(keyword: string): Promise<FlightData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Flight WHERE flightNum LIKE ? ORDER BY flightNum',
                [`%${keyword}%`]
            );
            return rows as FlightData[];
        } finally {
            connection.release();
        }
    }

    // 根据出发城市查询航班
    static async searchFlightsByFromCity(city: string): Promise<FlightData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Flight WHERE fromCity LIKE ? ORDER BY flightNum',
                [`%${city}%`]
            );
            return rows as FlightData[];
        } finally {
            connection.release();
        }
    }

    // 根据到达城市查询航班
    static async searchFlightsByArivCity(city: string): Promise<FlightData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Flight WHERE arivCity LIKE ? ORDER BY flightNum',
                [`%${city}%`]
            );
            return rows as FlightData[];
        } finally {
            connection.release();
        }
    }

    // 高级搜索（支持多个条件组合）
    static async advancedSearch(criteria: {
        flightNum?: string;
        fromCity?: string;
        arivCity?: string;
    }): Promise<FlightData[]> {
        const connection = await pool.getConnection();
        try {
            let query = 'SELECT * FROM Flight WHERE 1=1';
            const params: any[] = [];

            if (criteria.flightNum) {
                query += ' AND flightNum LIKE ?';
                params.push(`%${criteria.flightNum}%`);
            }

            if (criteria.fromCity) {
                query += ' AND fromCity LIKE ?';
                params.push(`%${criteria.fromCity}%`);
            }

            if (criteria.arivCity) {
                query += ' AND arivCity LIKE ?';
                params.push(`%${criteria.arivCity}%`);
            }

            query += ' ORDER BY flightNum';

            const [rows] = await connection.execute(query, params);
            return rows as FlightData[];
        } finally {
            connection.release();
        }
    }

    // 更新航班数据
    static async updateFlight(flightData: FlightData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Flight SET 
                 fromCity = ?, 
                 arivCity = ?, 
                 price = ?, 
                 totalSeat = ?, 
                 availSeat = ?
                 WHERE flightNum = ?`,
                [flightData.fromCity, flightData.arivCity, flightData.price, 
                 flightData.totalSeat, flightData.availSeat, flightData.flightNum]
            );

            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 可用座位数量增加
    static async incrementFlightAvailability(flightNum: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Flight 
                 SET availSeat = availSeat + 1 
                 WHERE flightNum = ? 
                 AND availSeat < totalSeat`,
                [flightNum]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 可用座位数量减少
    static async decrementFlightAvailability(flightNum: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Flight 
                 SET availSeat = availSeat - 1 
                 WHERE flightNum = ? 
                 AND availSeat > 0`,
                [flightNum]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 删除航班
    static async deleteFlight(flightNum: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                'DELETE FROM Flight WHERE flightNum = ?',
                [flightNum]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 根据航班号精确查询航班
    static async getFlightByFlightNum(flightNum: string): Promise<FlightData | null> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Flight WHERE flightNum = ?',
                [flightNum]
            );
            const flights = rows as FlightData[];
            return flights.length > 0 ? flights[0] : null;
        } finally {
            connection.release();
        }
    }

    // 检查航班是否存在
    static async flightExists(flightNum: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT 1 FROM Flight WHERE flightNum = ?',
                [flightNum]
            );
            return (rows as any[]).length > 0;
        } finally {
            connection.release();
        }
    }
}